const { log } = require('console');
const db = require('../db'); // 假设你已经有一个数据库连接模块
const db_execute = async (sql, params) => {
    if (!params) {
        return db.execute(sql, params); // 没有参数直接执行
    } else {
        const new_params = params.map(item => {
            if (typeof item === 'number') {
                return item.toString();  // 如果是数字，转换成字符串
            }
            return item;
        });
        return db.execute(sql, new_params); // 执行查询并传递转换后的参数
    }
};

class Employee {
    // 添加员工
    static async addEmployee(employeeData) {
        try {
            const { name, gender, date_of_birth, email, phone_number, hire_date, position, department, salary, address, status } = employeeData;
            const query = `INSERT INTO employees 
                     (name, gender, date_of_birth, email, phone_number, hire_date, position, department, salary, address, status, created_at, updated_at) 
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`;
            const result = await db_execute(query, [name, gender, date_of_birth, email, phone_number, hire_date, position, department, salary, address, status]);
            return result[0].insertId; // 返回新插入员工的ID
        } catch (err) {
            throw new Error('添加员工失败: ' + err.message);
        }
    }

    // 获取所有员工
    static async getAllEmployees(page = 1, limit = 10, searchMsg) {
        try {
            const pageNum = page - 1;  // 页码从 0 开始
            const pageSize = limit;    // 每页条数

            const offset = pageNum * pageSize;  // 计算偏移量

            let searchWord = []
            let queryParams = []

            if (searchMsg.id) {
                searchWord.push(`employee_id = ? `)
                queryParams.push(Number(searchMsg.id))
            }
            if (searchMsg.name) {
                searchWord.push('name LIKE ? ');
                queryParams.push(`%${searchMsg.name}%`);
            }
            if (searchMsg.gender) {
                searchWord.push(`gender LIKE ? `)
                queryParams.push(`%${searchMsg.gender}%`)
            }
            queryParams.push(pageSize, offset)
            let whereSQL = searchWord.length > 0 ? "WHERE " + searchWord.join("AND ") : ""
            // 确保参数顺序正确，LIMIT 先，OFFSET 后
            const query = `SELECT * FROM employees ${whereSQL} LIMIT ? OFFSET ?`;
            // 确保参数类型是数字
            const [rows] = await db_execute(query, queryParams);

            const countQuery = `SELECT COUNT(*) AS total FROM employees ${whereSQL}`;
            queryParams = queryParams.slice(0, queryParams.length - 2)
            const [countResult] = await db_execute(countQuery, queryParams);

            const total = countResult[0].total;

            return {
                total,
                data: rows
            };
        } catch (err) {
            log("Error: ", err);  // 打印详细的错误信息，帮助调试
            throw new Error('获取员工数据失败: ' + err.message);
        }
    }

    // 根据员工ID获取员工
    static async getEmployeeById(employee_id) {
        try {
            const query = 'SELECT * FROM employees WHERE employee_id = ?';
            const [rows] = await db_execute(query, [employee_id]);
            return rows.length > 0 ? rows[0] : null; // 如果没有找到员工，返回 null
        } catch (err) {
            throw new Error('获取员工数据失败: ' + err.message);
        }
    }

    // 更新员工信息
    static async updateEmployee(employee_id, employeeData) {
        try {
            const { name, gender, date_of_birth, email, phone_number, hire_date, position, department, salary, address, status } = employeeData;
            const query = `UPDATE employees SET 
                     name = ?, gender = ?, date_of_birth = ?, email = ?, phone_number = ?, hire_date = ?, position = ?, 
                     department = ?, salary = ?, address = ?, status = ?, updated_at = NOW() 
                     WHERE employee_id = ?`;
            const result = await db_execute(query, [name, gender, date_of_birth, email, phone_number, hire_date, position, department, salary, address, status, employee_id]);
            return result[0].affectedRows; // 返回受影响的行数
        } catch (err) {
            throw new Error('更新员工失败: ' + err.message);
        }
    }

    // 删除员工
    static async deleteEmployee(employee_id) {
        try {
            const query = 'DELETE FROM employees WHERE employee_id = ?';
            const result = await db_execute(query, [employee_id]);
            return result[0].affectedRows; // 返回受影响的行数
        } catch (err) {
            throw new Error('删除员工失败: ' + err.message);
        }
    }
}

module.exports = Employee;
